# -*- encoding: utf-8 -*-
#import odoo.addons.decimal_precision as dp
from odoo import models, fields, api, _
from odoo.addons import base_cw
from odoo.exceptions import UserError
from odoo.tools.float_utils import float_round, float_compare


class statement_current_accounts(models.Model):
    _name = 'statement.current.accounts'
    _description = u'辅助明细查询'
    _rec_name = 'account_id'

    @api.depends('account_id')
    def _get_sub_account_ids(self):
        account_obj = self.env['cncw.account']
        self.sub_account_ids = account_obj.get_sub_account_ids(self.account_id)

    account_id = fields.Many2one('cncw.account', u'会计科目', required=True, ondelete="restrict")
    sub_account_id = fields.Many2one('res.partner', u'辅助核算', ondelete="restrict")
    sub_account_type = fields.Selection(base_cw.public.SUB_ACCOUNT_TYPE, u'辅助核算类型',readonly=True,
                                        related='account_id.sub_account_type')
    sub_account_ids = fields.Many2many('res.partner', compute='_get_sub_account_ids', string=u'可选辅助核算')
    subaccount_category_id = fields.Many2one('subaccount.category', u'辅助核算类别', readonly=True,
                                             related='account_id.subaccount_category_id')
    date_type = fields.Selection([('this_year', u'本年'),
                                  ('this_month', u'本月'),
                                  # ('today', u'今天'),
                                  ('other_month', u'其它期别')], u'日期范围', default='this_month')
    period_id = fields.Many2one('account.period', u'期别')
    start_date = fields.Date(u'起始日')
    end_date = fields.Date(u'截止日')
    line_ids = fields.One2many('statement.current.accounts.line', 'master_id', u'明细')

    @api.onchange('date_type')
    def onchange_date_type(self):
        if self.date_type == 'this_year':
            year = self.env['account.fiscalyear'].search([('date_start', '<=', fields.Date.context_today(self)),
                                                          ('date_stop', '>=', fields.Date.context_today(self))],
                                                         limit=1)
            if year:
                self.start_date = year.date_start
                self.end_date = year.date_stop

        elif self.date_type == 'this_month':
            period_id = self.env['account.period'].search([('date_start', '<=', fields.Date.context_today(self)),
                                                           ('date_stop', '>=', fields.Date.context_today(self))],
                                                          limit=1)
            if period_id:
                self.start_date = period_id.date_start
                self.end_date = period_id.date_stop
        elif self.date_type == 'today':
            self.start_date = fields.Date.context_today(self)
            self.end_date = fields.Date.context_today(self)
        elif self.date_type == 'other_month':
            pass

    @api.onchange('period_id')
    def onchange_period_id(self):
        if self.period_id:
            self.start_date = self.period_id.date_start
            self.end_date = self.period_id.date_stop


    def action_confirm(self):
        self.ensure_one()
        if not self.start_date:
            raise UserError(_(u'开始日期不可为空!'))

        if not self.end_date:
            raise UserError(_(u'截止日期不可为空!'))

        if not self.account_id:
            raise UserError(_(u'会科不可为空!'))
        if not self.sub_account_id:
            raise UserError(_(u'辅助核算不可为空!'))
        sql = """
            select statement_current_accounts_settle('%s','%s',%s,%s,%s,%s);
            """ % (self.start_date, self.end_date, self.account_id.id,self.sub_account_id.id, self.id, self._uid)
        self._cr.execute(sql)
        self._cr.commit()


class statement_current_accounts_line(models.Model):
    _name = 'statement.current.accounts.line'
    _description = u'辅助明细查询'

    master_id = fields.Many2one('statement.current.accounts', u'主档', ondelete="cascade")
    sequence = fields.Integer(u'序', default=1)
    date = fields.Date(u'日期')
    origin = fields.Char('源单据')
    name = fields.Char(u'凭证号')
    remark = fields.Char(u'摘要')
    account_id = fields.Many2one('cncw.account', u'会计科目', ondelete="set null")
    dc_type = fields.Selection(base_cw.public.DC_TYPE, u'借贷', )
    sub_account_id = fields.Many2one('res.partner', u'辅助核算', ondelete="set null")
    debit = fields.Float(u'借方金额', digits='Amount')
    credit = fields.Float(u'贷方金额', digits='Amount')
    balance = fields.Float(u'期末', digits='Amount')

    def init(self):
        self._cr.execute("""
                    CREATE OR REPLACE FUNCTION statement_current_accounts_settle(
                        start_date_t timestamp,
                        end_date_t timestamp,
                        account_id_t integer,
                        sub_account_id_t integer,
                        master_id_t integer,
                        uid integer)
                      RETURNS void AS
                    $BODY$
                    /*
                    来往明细查询
                    DROP FUNCTION statement_current_accounts_settle(timestamp,timestamp,integer,integer,integer,integer)

                    select statement_current_accounts_settle('2016-11-01','2016-11-30',308,7152,2,1)

                    select statement_current_accounts_balance_settle(11,10,2,1)
                    select *  from account_period order by id
                    select id from cncw_account where code='11310000'
                    select id,name from res_partner where code='0001'
                    select id from statement_current_accounts
                    select * from statement_current_accounts_line
                    */
                    DECLARE
                            pre_period_id_t integer;
                            pre_to_date timestamp;
                            period_id_t integer;
                        count_t integer;
                    begin
                            create temp table statement_current_accounts_line_t(
                          remark character varying, -- 摘要
                          name character varying, -- 凭证号
                          origin character varying, --原单据
                          sequence integer, -- 序
                          credit numeric, -- 贷方金额
                          debit numeric, -- 借方金额
                          date date, -- 日期
                          master_id integer, -- 主档
                          balance numeric, -- 期末
                           begin numeric, -- 期初
                          account_id integer, -- 序
                          sub_account_id integer, -- 序
                          dc_type character varying -- 序
                            );

                        select id into period_id_t from account_period where start_date_t between date_start and date_stop;
                            select id into pre_period_id_t from account_period where start_date_t-interval'1 D' between date_start and date_stop;

                            delete from statement_current_accounts_line where master_id=master_id_t;
                            --1.上期
                            insert into statement_current_accounts_line_t(sequence,date,remark,dc_type,account_id,sub_account_id,debit,credit,begin,balance,master_id)
                            select cast(1 as integer) as sequence,
                                   cast(start_date_t-interval'1 D' as timestamp) as date,
                                   cast('上期期末' as character varying) as remark,
                                   b.dc_type,account_id,sub_account_id,debit,credit,balance,balance,master_id_t
                              from statement_current_accounts_balance a left join cncw_account b on a.account_id=b.id
                             where period_id=pre_period_id_t
                               and account_id=account_id_t
                               and sub_account_id=sub_account_id_t;
                            select count(sequence) into count_t from statement_current_accounts_line_t;
                           if count_t=0 then
                              insert into statement_current_accounts_line_t(sequence,date,remark,dc_type,account_id,sub_account_id,master_id)
                              select cast(1 as integer) as sequence,
                                   cast(start_date_t-interval'1 D' as timestamp) as date,
                                   cast('上期期末' as character varying) as remark,
                                   cast('D' as character varying) as dc_type,
                                   account_id_t,sub_account_id_t,master_id_t;
                            end if;

                            -- 2.本期
                        insert into statement_current_accounts_line_t(sequence,date,name,origin,remark,dc_type,account_id,sub_account_id,debit,credit,balance,master_id)
                        select cast(2 as integer) as sequence,b.date,b.name,b.origin,a.name as remark,a.dc_type,a.account_id,d.sub_account_id,
                                coalesce(debit,0) as debit,
                                coalesce(credit,0) as credit,
                                (coalesce(debit,0)-coalesce(credit,0))*(case when c.dc_type='D' then 1 else -1 end) as balance,
                                master_id_t
                          from account_voucher_line a left join account_voucher b on a.master_id=b.id
                                                      left join cncw_account c on a.account_id=c.id
                                                      left join sub_account_line d on a.id=d.voucher_line_id

                         where b.state='audited'
                           and b.date between start_date_t and end_date_t
                           and a.account_id=account_id_t
                           and d.sub_account_id=sub_account_id_t
                           and a.account_id is not null
                           and d.sub_account_id is not null;
                        --3.本期小计
                        insert into statement_current_accounts_line_t(sequence,date,remark,dc_type,account_id,sub_account_id,debit,credit,balance,master_id)
                        select  cast(3 as integer) as sequence,end_date_t as date,cast('本期小计' as character varying) as remark,c.dc_type,a.account_id,d.sub_account_id,
                                sum(coalesce(debit,0)) as debit,
                                sum(coalesce(credit,0)) as credit,
                                sum((coalesce(debit,0)-coalesce(credit,0)))*(case when c.dc_type='D' then 1 else -1 end) as balance,
                                master_id_t
                          from account_voucher_line a left join account_voucher b on a.master_id=b.id
                                                      left join cncw_account c on a.account_id=c.id
                                                      left join sub_account_line d on a.id=d.voucher_line_id

                         where b.state='audited'
                           and b.date between start_date_t and end_date_t
                           and a.account_id=account_id_t
                           and d.sub_account_id=sub_account_id_t
                           and a.account_id is not null
                           and d.sub_account_id is not null
                             group by end_date_t,a.name,c.dc_type,d.sub_account_id,a.account_id;
                           -- 4 合计
                           insert into statement_current_accounts_line_t(sequence,date,remark,dc_type,account_id,sub_account_id,debit,credit,balance,master_id)
                           select cast(9 as integer) as sequence,end_date_t as date,cast('截止本期' as character varying) as remark,b.dc_type,a.account_id,a.sub_account_id,
                                  sum(coalesce(debit,0)) as debit,
                              sum(coalesce(credit,0)) as credit,
                              sum(coalesce(begin,0)+coalesce(debit,0)-coalesce(credit,0))*(case when b.dc_type='D' then 1 else -1 end) as balance,
                              master_id_t
                           from statement_current_accounts_line_t a left join cncw_account b on a.account_id=b.id
                           where sequence in (1,3)
                            --remark in ('上期期末','本期小计')
                           group by end_date_t,b.dc_type,a.account_id,a.sub_account_id;
                           -- 5.写入db
                           insert into statement_current_accounts_line(sequence,date,name,origin,remark,dc_type,account_id,sub_account_id,debit,credit,balance,master_id)
                           select sequence,date,name,origin,remark,dc_type,account_id,sub_account_id,debit,credit,balance,master_id
                           from statement_current_accounts_line_t;
                           drop table statement_current_accounts_line_t;
                     end;
                     $BODY$
                      LANGUAGE plpgsql VOLATILE
                      COST 100;
                     """)